package com.laicunba.util;

import java.io.BufferedInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

public class ExcelUtil {

	public static void main(String[] args) throws Exception {
		List<Map<String, Object>> list = new ArrayList();
		Map<String, Object> row1 = new HashMap();
		row1.put("c1", "列1,值1");
		row1.put("c2", "列2,值2");
		Map<String, Object> row2 = new HashMap();
		row2.put("c1", "列1,值1___");
		row2.put("c2", "列2,值2___");
		list.add(row1);
		list.add(row2);
		ByteArrayOutputStream stream = ExcelUtil.generate(list);
		FileOutputStream fos = new FileOutputStream("D:/来存吧/test.xls");
		fos.write(stream.toByteArray());
		fos.flush();
		fos.close();
	}

	public static ByteArrayOutputStream generateByJsonArray(JSONArray array) throws IOException {
		// 第一步，创建一个webbook，对应一个Excel文件
		HSSFWorkbook book = new HSSFWorkbook();
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = book.createSheet("sheet1");
		if (array != null && !array.isEmpty()) {
			HSSFRow headRow = sheet.createRow(0);
			HSSFCellStyle style = book.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setFillBackgroundColor((short) 0);
			Set<String> columnNameSet = ((JSONObject) array.get(0)).keySet();
			int i = 0;
			HSSFCell cell = null;
			for (String columnName : columnNameSet) {
				cell = headRow.createCell(i);
				cell.setCellValue(columnName);
				cell.setCellStyle(style);
				i++;
			}
			i = 1;
			Object obj = null;
			for (int j = 0; j < array.size(); j++) {
				JSONObject json = (JSONObject) array.get(j);
				HSSFRow newRowGrid = sheet.createRow(i);
				int x = 0;
				for (String columnName : columnNameSet) {
					cell = newRowGrid.createCell(x);
					obj = json.get(columnName);
					cell.setCellValue(obj == null ? "" : obj.toString());
					x++;
				}
				i++;
			}
		}
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		book.write(outputStream);
		return outputStream;
	}

	public static ByteArrayOutputStream generate(List<Map<String, Object>> data) throws Exception {
		// 第一步，创建一个webbook，对应一个Excel文件
		HSSFWorkbook book = new HSSFWorkbook();
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = book.createSheet("sheet1");
		if (data != null && !data.isEmpty()) {
			HSSFRow headRow = sheet.createRow(0);
			HSSFCellStyle style = book.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setFillBackgroundColor((short) 0);
			Set<String> columnNameSet = data.get(0).keySet();
			int i = 0;
			HSSFCell cell = null;
			for (String columnName : columnNameSet) {
				cell = headRow.createCell(i);
				cell.setCellValue(columnName);
				cell.setCellStyle(style);
				i++;
			}
			i = 1;
			Object obj=null;
			for (Map<String, Object> rowData : data) {
				HSSFRow newRowGrid = sheet.createRow(i);
				int x = 0;
				for (String columnName : columnNameSet) {
					cell = newRowGrid.createCell(x);
					obj=rowData.get(columnName);
					if(obj instanceof Date){
						cell.setCellValue(DateUtil.format((Date)obj));
					}else{
						cell.setCellValue(obj!=null?obj.toString():"");
					}
					x++;
				}
				i++;
			}
		}
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		book.write(outputStream);
		return outputStream;
	}
	
	/**
	 * 读取文件
	 * 
	 * @param readFile
	 */
    public static List<Map<String, String>> readExcel(File readFile) {  
    	InputStream is = null;
    	List<Map<String, String>> resultList = null;
    	try {
    		resultList = new ArrayList<Map<String, String>>();
    		is = new FileInputStream(readFile);
    		
    		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
    		// 循环Sheet
    		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
    			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
    			if (hssfSheet == null) {
    				continue;
    			}
    			//获取第一行标题
    			HSSFRow firstRow = hssfSheet.getRow(0);
    			// 循环行Row
    			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
    				Map<String, String> dataMap = null;
    				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
    				if (hssfRow == null) {
    					continue;
    				}
    				dataMap = new HashMap<String, String>();
    				// 循环列Cell
    				for(int cellNum = 0; cellNum <= hssfRow.getPhysicalNumberOfCells(); cellNum++) {
    					HSSFCell firstRowCell = firstRow.getCell(cellNum); // 第一行对应的列
    					HSSFCell hssfCell = hssfRow.getCell(cellNum);
    					dataMap.put(getValue(firstRowCell), getValue(hssfCell));
    				}
    				resultList.add(dataMap);
    			}
    		}
    	} catch (FileNotFoundException e) {
    		e.printStackTrace();
    	} catch (IOException e) {
    		e.printStackTrace();
    	}
    	return resultList;
    }
    // 得到Excel列的值
    @SuppressWarnings("static-access")
    private static String getValue(HSSFCell hssfCell) {
    	if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
    		return String.valueOf(hssfCell.getBooleanCellValue());
    	} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
    		return String.valueOf(hssfCell.getNumericCellValue());
    	} else {
    		return String.valueOf(hssfCell.getStringCellValue());
    	}
    }
    
    public static List<Map<String, String>> readXlsxExcelByFile(File readFile) {  
    	FileInputStream input = null;
		try {
			input = new FileInputStream(readFile);
			return readXlsxExcel(input);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} finally{
			try {
				if (input != null) {
					input.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
    	}
		return null;
    }
    
    public static List<Map<String, String>> readXlsxExcelByStream(FileInputStream input) {  
    	return readXlsxExcel(input);
    }
    
    /**
   	 * 读取文件
   	 * 
   	 * @param readFile
   	 */
    private static List<Map<String, String>> readXlsxExcel(FileInputStream input) {  
    	List<Map<String, String>> resultList = null;
    	try {
    		resultList = new ArrayList<Map<String, String>>();
    		
    		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new BufferedInputStream(input)); 
    		// 读表
    		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
    			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
    			if (xssfSheet == null) {
    				continue;
    			}
    			//获取第一行标题
    			XSSFRow firstRow = xssfSheet.getRow(0);
    			// 读行
    			for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
    				Map<String, String> dataMap = null;
    				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
    				if (xssfRow == null) {
    					continue;
    				}
    				dataMap = new HashMap<String, String>();
    				// 读列
    				for(int cellNum = 0; cellNum < xssfRow.getPhysicalNumberOfCells(); cellNum++) {
    					XSSFCell firstRowCell = firstRow.getCell(cellNum); // 第一行对应的列
    					XSSFCell xssfCell = xssfRow.getCell(cellNum);
    					dataMap.put(getXlsxValue(firstRowCell),getXlsxValue(xssfCell));
    				}
    				resultList.add(dataMap);
    			}
    		}
    	} catch (FileNotFoundException e) {
    		e.printStackTrace();
    	} catch (IOException e) {
    		e.printStackTrace();
    	}
    	return resultList;
    }
    
    // 得到Excel列的值
    private static String getXlsxValue(XSSFCell xssfCell) {
    	if (xssfCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
    		return String.valueOf(xssfCell.getBooleanCellValue());
    	} else if (xssfCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
    		DecimalFormat df = new DecimalFormat("0");  
    		return df.format(xssfCell.getNumericCellValue());
    	} else {
    		return String.valueOf(xssfCell.getStringCellValue());
    	}
    }

    
}
